用SQL Server处理物料清单规则

/* count the ingredients required */
SELECT 
      [Recipe Ingredients].RecipeID, 
      Count([Recipe Ingredients].IngredientID) AS Required
FROM 
      [Recipe Ingredients]
GROUP BY 
      [Recipe Ingredients].RecipeID;


/* count the ingredients available */
SELECT 
      [Recipe Ingredients].RecipeID, 
      Count(Pantry.IngredientID) AS Available
FROM Pantry 
      RIGHT JOIN [Recipe Ingredients] 
      ON Pantry.IngredientID = [Recipe Ingredients].IngredientID
GROUP BY 
      [Recipe Ingredients].RecipeID;